﻿using AdinZ.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


/**
* @Author: AdinZ
* @Q  Q : 1454269
* @Email:adinz@adinz.com
* @Created: 2014/3/31 16:36:20
*
* */
namespace Novel.Sys.Dal
{
    public class novel_article
    {
        public int Add(Model.novel_article model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO [novel_article] ([tid], [title], [author], [intor], [chaptersize], [size], [hits], [created], [cover], [flag], [STATUS], [permission], [FIRST], [isfull], [display],[hot],[recommend],[strong]) VALUES (");
            strSql.Append("@tid,@title,@author,@intor,@chaptersize,@size,@hits,@created,@cover,@flag,@STATUS,@permission,@FIRST,@isfull,@display,@hot,@recommend,@strong");
            strSql.Append(")select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@tid", SqlDbType.Int),
                    new SqlParameter("@title", SqlDbType.NVarChar,200),
                    new SqlParameter("@author", SqlDbType.NVarChar,100),
                    new SqlParameter("@intor", SqlDbType.NVarChar,1000),
                    new SqlParameter("@chaptersize", SqlDbType.Int),
                    new SqlParameter("@size", SqlDbType.Int),
                    new SqlParameter("@hits", SqlDbType.Int),
                    new SqlParameter("@created", SqlDbType.DateTime),
                    new SqlParameter("@cover", SqlDbType.NVarChar,200),
                    new SqlParameter("@flag", SqlDbType.NVarChar,50),
                    new SqlParameter("@STATUS", SqlDbType.Int),
                    new SqlParameter("@permission", SqlDbType.Int),
                    new SqlParameter("@FIRST", SqlDbType.Int),
                    new SqlParameter("@isfull", SqlDbType.Int),
                    new SqlParameter("@display", SqlDbType.Int),
                    new SqlParameter("@hot", SqlDbType.Int),
                    new SqlParameter("@recommend", SqlDbType.Int),
                    new SqlParameter("@strong", SqlDbType.Int)
                                        };
            parameters[0].Value = model.Tid;
            parameters[1].Value = model.Title;
            parameters[2].Value = model.Author;
            parameters[3].Value = model.Intor;
            parameters[4].Value = model.Chaptersize;
            parameters[5].Value = model.Size;
            parameters[6].Value = model.Hits;
            parameters[7].Value = model.Created;
            parameters[8].Value = model.Cover;
            parameters[9].Value = model.Flag;
            parameters[10].Value = model.Status;
            parameters[11].Value = model.Permission;
            parameters[12].Value = model.First;
            parameters[13].Value = model.Isfull;
            parameters[14].Value = model.Display;
            object obj = DbHelpers.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        public void UpdateCover(int ID, string Cover)
        {
            string sql = "UPDATE [novel_article] SET [cover]='" + Cover + "' WHERE ([ID]='"+ID+"')";
            DbHelpers.NonQuery(sql);
        }
        public bool Exists(string title, string author)
        {//select count(1) from [novel_article] WHERE title='123' AND author='1'
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [novel_article]");
            strSql.Append(" where title=@title and author=@author");
            SqlParameter[] parameters = {
                    new SqlParameter("@title", SqlDbType.NVarChar,200),
                    new SqlParameter("@author", SqlDbType.NVarChar,100)};
            parameters[0].Value = title;
            parameters[1].Value = author;
            return DbHelpers.Exists(strSql.ToString(), parameters);

        }
        /// <summary>
        /// 获取本文id
        /// </summary>
        /// <param name="title"></param>
        /// <param name="author"></param>
        /// <returns></returns>
        public int GetID(string title, string author)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID from [novel_article]");
            strSql.Append(" where title=@title and author=@author");
            SqlParameter[] parameters = {
                    new SqlParameter("@title", SqlDbType.NVarChar,200),
                    new SqlParameter("@author", SqlDbType.NVarChar,100)};
            parameters[0].Value = title;
            parameters[1].Value = author;
            DataSet ds= DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    return int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                else
                    return 0;
            }
            else { return 0; }
        }

        /// <summary>
        /// 根据id获取书名
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public string GetNovelName(int aid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select title from [novel_article] where ID=@ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = aid;
            DataSet ds = DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["title"] != null && ds.Tables[0].Rows[0]["title"].ToString() != "")
                {
                    return ds.Tables[0].Rows[0]["title"].ToString();
                }
                else
                    return "";
            }
            else
                return "";

        }
        /// <summary>
        /// 根据id获取作者
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public string GetAuthor(int aid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select author from [novel_article] where ID=@ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = aid;
            DataSet ds = DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["author"] != null && ds.Tables[0].Rows[0]["author"].ToString() != "")
                {
                    return ds.Tables[0].Rows[0]["author"].ToString();
                }
                else
                    return "";
            }
            else
                return "";

        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM novel_article ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelpers.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strWhere = " Where " + strWhere;
            }

            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                orderby = " Order By " + orderby;
            }

            //startIndex = startIndex - 1;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM novel_article Where ID Not IN ");
            strSql.Append("(Select Top " + startIndex + " ID From novel_article" + strWhere + orderby + ")");
            strSql.Append(" And ID In ");
            strSql.Append("(Select Top " + endIndex + " ID From novel_article" + strWhere + orderby + ")");
            strSql.Append(orderby);
            return DbHelpers.GetDataSet(strSql.ToString());
        }

        /// <summary>
        /// 获取不是本地的数据
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public DataSet GetRemoteList()
        {
            string sql = "select  id,cover from [novel_article] where local=0";
            return DbHelpers.GetDataSet(sql.ToString());
        }

        /// <summary>
        /// 判断封面是否本地了
        /// true 已经本地
        /// </summary>
        /// <param name="id"></param>
        /// <returns>true 已经本地/</returns>
        public bool ExistsCover(int id)
        {
            string sql = "select count(1) from [novel_article]  where id=" + id + " and local=1";
            return DbHelpers.Exists(sql);
        }


        /// <summary>
        /// 更新封面
        /// </summary>
        /// <param name="id"></param>
        /// <param name="cover"></param>
        /// <param name="local">1=本地,0=远程</param>
        /// <returns></returns>
        public int UpdateCover(int id, string cover,int local)
        {
            string sql = "UPDATE novel_article SET [local] = '" + local + "' ,  [cover]='" + cover + "' WHERE id='" + id + "'";
            return DbHelpers.NonQuery(sql);
        }

        /// <summary>
        /// 获取一个对象实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Model.novel_article GetModel(int ID)
        {
            Model.novel_article model = new Model.novel_article();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [novel_article] where ID=@ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = ID;
            DataSet ds = DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["tid"] != null && ds.Tables[0].Rows[0]["tid"].ToString() != "")
                {
                    model.Tid = int.Parse(ds.Tables[0].Rows[0]["tid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["title"] != null && ds.Tables[0].Rows[0]["title"].ToString() != "")
                {
                    model.Title = ds.Tables[0].Rows[0]["title"].ToString();
                }
                if (ds.Tables[0].Rows[0]["author"] != null && ds.Tables[0].Rows[0]["author"].ToString() != "")
                {
                    model.Author = ds.Tables[0].Rows[0]["author"].ToString();
                }
                if (ds.Tables[0].Rows[0]["intor"] != null && ds.Tables[0].Rows[0]["intor"].ToString() != "")
                {
                    model.Intor = ds.Tables[0].Rows[0]["intor"].ToString();
                }
                if (ds.Tables[0].Rows[0]["chaptersize"] != null && ds.Tables[0].Rows[0]["chaptersize"].ToString() != "")
                {
                    model.Chaptersize = int.Parse(ds.Tables[0].Rows[0]["chaptersize"].ToString());
                }
                if (ds.Tables[0].Rows[0]["size"] != null && ds.Tables[0].Rows[0]["size"].ToString() != "")
                {
                    model.Size = int.Parse(ds.Tables[0].Rows[0]["size"].ToString());
                }
                if (ds.Tables[0].Rows[0]["hits"] != null && ds.Tables[0].Rows[0]["hits"].ToString() != "")
                {
                    model.Hits = int.Parse(ds.Tables[0].Rows[0]["hits"].ToString());
                }
                if (ds.Tables[0].Rows[0]["created"] != null && ds.Tables[0].Rows[0]["created"].ToString() != "")
                {
                    model.Created = DateTime.Parse(ds.Tables[0].Rows[0]["created"].ToString());
                }
                if (ds.Tables[0].Rows[0]["cover"] != null && ds.Tables[0].Rows[0]["cover"].ToString() != "")
                {
                    model.Cover = ds.Tables[0].Rows[0]["cover"].ToString();
                }
                if (ds.Tables[0].Rows[0]["flag"] != null && ds.Tables[0].Rows[0]["flag"].ToString() != "")
                {
                    model.Flag = ds.Tables[0].Rows[0]["flag"].ToString();
                }
                if (ds.Tables[0].Rows[0]["STATUS"] != null && ds.Tables[0].Rows[0]["STATUS"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["STATUS"].ToString());
                }
                if (ds.Tables[0].Rows[0]["permission"] != null && ds.Tables[0].Rows[0]["permission"].ToString() != "")
                {
                    model.Permission = int.Parse(ds.Tables[0].Rows[0]["permission"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FIRST"] != null && ds.Tables[0].Rows[0]["FIRST"].ToString() != "")
                {
                    model.First = int.Parse(ds.Tables[0].Rows[0]["FIRST"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isfull"] != null && ds.Tables[0].Rows[0]["isfull"].ToString() != "")
                {
                    model.Isfull = int.Parse(ds.Tables[0].Rows[0]["isfull"].ToString());
                }
                if (ds.Tables[0].Rows[0]["display"] != null && ds.Tables[0].Rows[0]["display"].ToString() != "")
                {
                    model.Display = int.Parse(ds.Tables[0].Rows[0]["display"].ToString());
                }
                if (ds.Tables[0].Rows[0]["hot"] != null && ds.Tables[0].Rows[0]["hot"].ToString() != "")
                {
                    model.Hot = int.Parse(ds.Tables[0].Rows[0]["hot"].ToString());
                }
                else
                    model.Hot = 0;
                if (ds.Tables[0].Rows[0]["recommend"] != null && ds.Tables[0].Rows[0]["recommend"].ToString() != "")
                {
                    model.Recommend = int.Parse(ds.Tables[0].Rows[0]["recommend"].ToString());
                }
                else
                    model.Recommend = 0;
                if (ds.Tables[0].Rows[0]["strong"] != null && ds.Tables[0].Rows[0]["strong"].ToString() != "")
                {
                    model.Strong = int.Parse(ds.Tables[0].Rows[0]["strong"].ToString());
                }
                else
                    model.Strong = 0;
                if (ds.Tables[0].Rows[0]["local"] != null && ds.Tables[0].Rows[0]["local"].ToString() != "")
                {
                    model.Local = int.Parse(ds.Tables[0].Rows[0]["local"].ToString());
                }
                else
                    model.Local = 0;

                return model;
            }
            else
            {
               return null;
            }

        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM novel_article ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelpers.GetDataSet(strSql.ToString());
        }


        /// <summary>
        /// 修改点击数
        /// </summary>
        /// <param name="id"></param>
        /// <param name="hits"></param>
        public void UpdateHits(int id,int hits)
        {
            string sql = "update [novel_article]  set [hits]='" + hits + "' where [id]='" + id + "'";
            DbHelpers.NonQuery(sql);
        }
        /// <summary>
        /// 获取点击数
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int GetHits(int ID)
        {
            string sql = "select hits FROM novel_article where id=" + ID;
            object obj = DbHelpers.GetSingle(sql);
            int cmdresult;
            if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            return cmdresult;
        }


        /// <summary>
        /// 修改下载数
        /// </summary>
        /// <param name="id"></param>
        /// <param name="downloads"></param>
        public void UpdateDownloads(int id, int downloads)
        {
            string sql = "update [novel_article]  set [download]='" + downloads + "' where [id]='" + id + "'";
            DbHelpers.NonQuery(sql);
        }
        /// <summary>
        /// 获取下载数
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int GetDownloads(int ID)
        {
            string sql = "select download FROM novel_article where id=" + ID;
            object obj = DbHelpers.GetSingle(sql);
            int cmdresult;
            if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            return cmdresult;
        }




    }

}


